package com.ht.api.service;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.ht.api.business.ad.PlanHelper;
import com.ht.api.db.*;
import com.ht.api.db.task.HtTaskExecutor;
import com.ht.api.db.task.TaskGroup;
import com.ht.api.db.task.TaskHandler;
import com.ht.api.db.util.HtUtil;
import com.ht.api.task.ad.CreatePlanTask;
import com.ht.api.task.ad.CreatePlanTaskCallback;
import com.ht.api.task.ad.CreateSopUserAdTask;
import com.ht.api.task.ad.CreateSopUserAdTaskCallback;
import com.ht.api.util.RuleSqlUtil;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.io.IOException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;

@Slf4j
public class SopService {

    /**
     * 刷新旅程用户
     * @param ds
     * @param config
     * @param sop
     */
    public static void refreshSopUser (DataSource ds, StreamLoadConfig config, Entity sop, boolean isInit) {
        StringBuilder sopUsersSql = StrUtil.builder();

        // 查询旅程人群范围
        String audienceSql = "select bitmap_union(to_bitmap(oneid)) oneid from audience_relation where status = 0 and audience_id in (" + sop.getJSONArray("audience_rule").join(",") + ") ";

        StringBuilder appSql = PlanHelper.buildAppSql(sop.getJSONObject("app_rule"));

        sopUsersSql.append("(");

        sopUsersSql.append("select bitmap_and(t1.oneid, t2.oneid) oneid from ").append("(" + audienceSql + ") t1,").append("(" + appSql + ") t2 ");

        sopUsersSql.append(") sopuser, ");
        //查询正常状态下的旅程阶段，并排序
        SqlParser parser = Db.createSqlParser("stage").where("sop_id", sop.getMainId()).order("order_num", "desc");
        if (!isInit) {
            parser.where("status", 1);
        }
        DbList stageList = parser.query(ds);

        JSONArray preStages = JSONUtil.createArray();// 用于做下一个阶段的前置信息
        for (Entity stage : stageList) {
            List<Object> paramList = new ArrayList<>();

            // 组装查询符合阶段条件的用户，包括新用户，和不满足条件需要退出的用户
            String sql = buildStageUserSql(stage, preStages, sopUsersSql, paramList);

            Entity stageUsers = Db.findOne(ds, sql, paramList);// 需要处理的用户

            // 对需要处理的用户做数据组装，以及保存
            buildAndSaveStageUser(ds, config, stage, stageUsers);
        }
    }

    /**
     * 组装查询满足阶段条件的用户sql
     * @param stage
     * @param preStages
     * @param sopUsersSql
     * @param paramList
     * @return
     */
    public static String buildStageUserSql (Entity stage, JSONArray preStages, StringBuilder sopUsersSql, List<Object> paramList) {
        int coolingDays = stage.isNull("cooling_days") ? 0 : stage.getInt("cooling_days");

        StringBuilder stageUsersSql = StrUtil.builder();

        if (!preStages.isEmpty()) {
            stageUsersSql.append("(").append("select bitmap_union(to_bitmap(oneid)) oneid from ad_plan_user where source_id in ("+preStages.join(",")+")").append(") preusers, ");
        } else {
            stageUsersSql.append("(select bitmap_union(to_bitmap(1)) oneid)").append(" preusers, ");
        }

        preStages.add(stage.getMainId());

        String conditionSql = RuleSqlUtil.buildConditionSql(stage.getJSONObject("condition"), paramList);//构建条件sql
        stageUsersSql.append("("+conditionSql+") conditionuser ,");

        stageUsersSql.append("(select bitmap_union(to_bitmap(oneid)) oneid from ad_plan_user where source_id = " + stage.getMainId()).append(") alluser,");
        stageUsersSql.append("(select bitmap_union(to_bitmap(oneid)) oneid from ad_plan_user where source_id = " + stage.getMainId()).append(" and status in (1,2)) normaluser,");

        //找到重复满足条件的用户，需要结合冷却天数进行判断
        stageUsersSql.append("(select bitmap_union(to_bitmap(oneid)) oneid from ad_plan_user where DATEDIFF(NOW(), exit_time) > "+coolingDays+" and source_id = " + stage.getMainId()).append(" and status in (3,4)) pauseuser  ");

        StringBuilder sql = StrUtil.builder();
        sql.append("select ");
        sql.append("bitmap_to_string(bitmap_not(bitmap_and(sopuser.oneid, bitmap_not(conditionuser.id, alluser.oneid)), preusers.oneid)) newuser_ids,");//第一次满足条件的
        sql.append("bitmap_to_string(bitmap_not(bitmap_and(sopuser.oneid, bitmap_and(conditionuser.id, pauseuser.oneid)), preusers.oneid)) repeatuser_ids,");//重复满足条件的
        sql.append("bitmap_to_string(bitmap_not(bitmap_and(sopuser.oneid, bitmap_not(normaluser.oneid, conditionuser.id)), preusers.oneid)) exituser_ids,");//之前正常，当前又不满足条件的
        sql.append("bitmap_to_string(bitmap_not(bitmap_not(normaluser.oneid, sopuser.oneid), preusers.oneid)) exitsopuser_ids ");//因不满足旅程人群而退出的

        sql.append(" from ").append(sopUsersSql.toString()).append(stageUsersSql.toString());

        return sql.toString();
    }

    /**
     * 组装阶段用户，并保存
     * @param config
     * @param stage
     * @param stageUsers
     */
    public static void buildAndSaveStageUser (DataSource ds, StreamLoadConfig config, Entity stage, Entity stageUsers) {
        String newuserIds = stageUsers.getStr("newuser_ids"); //新用户
        String repeatuserIds = stageUsers.getStr("repeatuser_ids");//重复满足条件的用户，之前是不满足
        String exituserIds = stageUsers.getStr("exituser_ids");//退出的用户
        String exitsopuserIds = stageUsers.getStr("exitsopuser_ids");//因不满足sop而需要退出的用户

        String planId = stage.getStr("plan_id");
        String campaignId = stage.getStr("campaign_id");
        String stageName = stage.getStr("name");

        List<Entity> dataList = new ArrayList<>();
        String eventTime = DateUtil.now();

        if (StrUtil.isNotBlank(newuserIds)) {
            for (String id : newuserIds.split(",")) {//第一次满足条件的
                Entity planUser = Entity.create("ad_plan_user");
                planUser.set("oneid", id);
                planUser.set("source_id", stage.getMainId());
                planUser.set("source_type", "sop");
                planUser.set("plan_id", planId);
                planUser.set("first_in_time", eventTime);
                planUser.set("last_in_time", eventTime);
                planUser.set("push_days", 0);
                planUser.set("status", 2);

                dataList.add(planUser);

                Entity adPlanUserLogs = Entity.create("ad_plan_user_logs");
                adPlanUserLogs.set("oneid", id);
                adPlanUserLogs.set("plan_id", planId);
                adPlanUserLogs.set("time", eventTime);
                adPlanUserLogs.set("status", 2);
                adPlanUserLogs.set("msg", "满足[" + stageName + "]阶段条件进入");
                dataList.add(adPlanUserLogs);
            }
        }

        if (StrUtil.isNotBlank(repeatuserIds)) {
            for (String id : repeatuserIds.split(",")) {//重复满足条件的
                Entity planUser = Entity.create("ad_plan_user");
                planUser.set("oneid", id);
                planUser.set("plan_id", planId);
                planUser.set("last_in_time", eventTime);
                planUser.set("status", 2);

                dataList.add(planUser);

                Entity adPlanUserLogs = Entity.create("ad_plan_user_logs");
                adPlanUserLogs.set("oneid", id);
                adPlanUserLogs.set("plan_id", planId);
                adPlanUserLogs.set("time", eventTime);
                adPlanUserLogs.set("status", 2);
                adPlanUserLogs.set("msg", "重新满足[" + stageName + "]阶段条件进入");
                dataList.add(adPlanUserLogs);
            }
        }

        if (StrUtil.isNotBlank(exituserIds)) {
            List<String> planUserIds = new ArrayList<>();
            for (String id : exituserIds.split(",")) {//之前正常，当前又不满足条件的
                Entity planUser = Entity.create("ad_plan_user");
                planUser.set("oneid", id);
                planUser.set("plan_id", planId);
                planUser.set("exit_time", eventTime);
                planUser.set("status", 3);

                dataList.add(planUser);

                Entity adPlanUserLogs = Entity.create("ad_plan_user_logs");
                adPlanUserLogs.set("oneid", id);
                adPlanUserLogs.set("plan_id", planId);
                adPlanUserLogs.set("time", eventTime);
                adPlanUserLogs.set("status", 3);
                adPlanUserLogs.set("msg", "完成[" + stageName + "]阶段跃迁");
                dataList.add(adPlanUserLogs);

                planUserIds.add(HtUtil.uniqueStrToNum(id + planId));
            }

            DbList taskList = Db.createSqlParser("ad_user_task").select("main_id").where("campaign_id", campaignId).where("plan_user_id", "in", planUserIds).where("status",1).query(ds);
            for (Entity task : taskList) {
                task.set("status", 4);
                task.set("result_msg", "因用户完成[" + stageName + "]阶段跃迁，取消任务");
                dataList.add(task);
            }
        }

        if (StrUtil.isNotBlank(exitsopuserIds)) {
            List<String> planUserIds = new ArrayList<>();

            for (String id : exitsopuserIds.split(",")) {//因不满足旅程人群而退出的
                Entity planUser = Entity.create("ad_plan_user");
                planUser.set("oneid", id);
                planUser.set("source_id", stage.getMainId());
                planUser.set("exit_time", eventTime);
                planUser.set("status", 4);

                dataList.add(planUser);

                Entity adPlanUserLogs = Entity.create("ad_plan_user_logs");
                adPlanUserLogs.set("oneid", id);
                adPlanUserLogs.set("plan_id", planId);
                adPlanUserLogs.set("time", eventTime);
                adPlanUserLogs.set("status", 4);
                adPlanUserLogs.set("msg", "不满足旅程条件退出");
                dataList.add(adPlanUserLogs);

                planUserIds.add(HtUtil.uniqueStrToNum(id + planId));
            }

            DbList taskList = Db.createSqlParser("ad_user_task").select("main_id").where("campaign_id", campaignId).where("plan_user_id", "in", planUserIds).where("status",1).query(ds);
            for (Entity task : taskList) {
                task.set("status", 4);
                task.set("result_msg", "因用户不满足旅程条件，取消任务");
                dataList.add(task);
            }
        }

        Db.insertBatchWithStream(config, dataList);
    }



    public static boolean isOverDays(String lastTimeStr, int intervalDays) {
        if (lastTimeStr == null || lastTimeStr.isEmpty()) {
            return true;
        }

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        LocalDateTime lastTime = LocalDateTime.parse(lastTimeStr, formatter);
        long daysBetween = ChronoUnit.DAYS.between(lastTime, LocalDateTime.now());
        return daysBetween >= intervalDays;
    }

    public static void planSopAd (long tenantId) {
        DataSource ds = DbConfig.tenantDSMap.get(tenantId);
        StreamLoadConfig config = DbConfig.useTenantSLC(tenantId);

        String oneids = Db.val(ds, "select bitmap_to_string(bitmap_union(to_bitmap(oneid))) oneids from ad_plan_user where and source_type = 'sop' and status in (1, 2)");

        if (StrUtil.isBlank(oneids)) {
            return;
        }

        JSONObject paramGroup = JSONUtil.createObj();
        paramGroup.set("tenant_id", tenantId);
        TaskGroup group = TaskGroup.create(paramGroup, oneids.split(",").length, new CreateSopUserAdTaskCallback());
        TaskHandler task = new CreateSopUserAdTask(); // 任务执行类

        int k = 0;
        List<Entity> dataList = new ArrayList<>();
        String time = DateUtil.now();
        for (String oneid : oneids.split(",")) {

            DbList userList = Db.query(ds, "select u.*, p.campaign_id, p.priority, p.app_rule from ad_plan_user u left join ad_plan p on u.plan_id = p.main_id where u.source_type = 'sop' and u.status in (1, 2) and u.oneid = ? order by p.priority asc, u.first_in_time asc ", oneid);

            for (int i = 0; i < userList.size(); i++) {
                Entity planUser = userList.get(i);
                int status = planUser.getInt("status");
                planUser.setTable("ad_plan_user");

                boolean isSavePlanUser = false;
                Entity adPlanUserLogs = Entity.create("ad_plan_user_logs");
                adPlanUserLogs.set("oneid", oneid);
                adPlanUserLogs.set("plan_id", planUser.get("plan_id"));
                adPlanUserLogs.set("time", time);

                if (i == 0) {
                    isSavePlanUser = true;
                    planUser.set("first_scheduling_time", time);
                    planUser.set("last_scheduling_time", time);
                    if (status == 0) {
                        planUser.set("status", 1);
                        adPlanUserLogs.set("status", 1);
                    }

                    JSONObject paramObj = JSONUtil.createObj();
                    paramObj.set("tenant_id", tenantId);
                    paramObj.set("planUser", planUser);
                    paramObj.set("num", k++);

                    HtTaskExecutor.submitTask(group, paramObj, task);

                } else {
                    if (status == 1) {
                        isSavePlanUser = true;

                        planUser.set("status", 2);
                        adPlanUserLogs.set("status", 2);
                    }
                }

                if (isSavePlanUser) {
                    dataList.add(planUser);
                }
            }

        }

        Db.insertBatchWithStream(config, dataList);
    }



    public static List<Entity> planAdToUser (long tenantId, DataSource ds, Entity planUser) {

        String oneid = planUser.getStr("oneid");

        Map<String, Integer> positionMap = new HashMap<>();
        positionMap.put("moment", 3);
//        positionMap.put("private_chat", 1);
//        positionMap.put("web_top", 1);
//        positionMap.put("web_bottom", 2);
//        positionMap.put("web_pop", 1);

        for (String position : positionMap.keySet()) {

            int limitCount = positionMap.get(position);

            int count = findNoPushCountWithUser(ds, position, oneid);

            if (limitCount - count <= 0) {
                continue;
            }

            Entity touchUser = PlanHelper.findTouchUser(tenantId, oneid, position, planUser.getJSONObject("app_rule"));

            if (touchUser == null) {
                continue;
            }

            List<Entity> taskList = createAdTaskToUser(ds, planUser, touchUser, position, limitCount);

            return taskList;
        }

        return null;

    }


    public static int findNoPushCountWithUser (DataSource ds, String position, String oneid) {

        StringBuilder limitCountSql = StrUtil.builder();
        limitCountSql.append("select ifnull(sum(sys_deleted),0) count from ad_user_task ")
                .append("where status = 1 ")
                .append("and ad_position = '" + position + "' ")
                .append("and bitmap_contains((select bitmap_union(to_bitmap(main_id)) from user_origin where oneid = "+oneid+"), origin_id)");

        Long count = Db.val(ds, limitCountSql.toString());

        return count.intValue();
    }

    public static List<Entity> createAdTaskToUser (DataSource ds, Entity planUser, Entity touchUser, String position, int limitCount) {

        String inTime = planUser.getStr("first_in_time");
        String oneid = planUser.getStr("oneid");
        String planId = planUser.getStr("plan_id");
        String campaignId = planUser.getStr("campaign_id");

        int delayDays = getDaysBetween(inTime);

        JSONArray campaignItemIds = JSONUtil.createArray();
        List<Entity> taskList = new ArrayList<>();
        List<Entity> succCampaignItems = new ArrayList<>();
        List<Entity> failCampaignItems = new ArrayList<>();

        int currentCount = limitCount;

        do {
            Map<String, List<Entity>> resultMap = findCreative (ds, touchUser, oneid, planId, campaignId, planUser.getMainId(), planUser.getStr("priority"), position, delayDays, currentCount, campaignItemIds);
            if (resultMap == null) {
                break;
            }

            taskList.addAll(resultMap.get("taskList"));
            succCampaignItems.addAll(resultMap.get("succCampaignItems"));
            failCampaignItems.addAll(resultMap.get("failCampaignItems"));

            for (Entity succCampaignItem : resultMap.get("succCampaignItems")) {
                campaignItemIds.add(succCampaignItem.getStr("main_id"));
            }

            for (Entity failCampaignItem : resultMap.get("failCampaignItems")) {
                campaignItemIds.add(failCampaignItem.getStr("main_id"));
            }

            currentCount = limitCount - taskList.size();

        } while (currentCount > 0);


        return taskList;
    }

    public static Map<String, List<Entity>> findCreative (DataSource ds, Entity touchUser, String oneid, String planId, String campaignId, String planUserId, String priority, String position, int delayDays, int limitCount, JSONArray campaignItemIds) {
        String sql = """
                SELECT
                	*
                FROM
                	ad_campaign_item
                WHERE
                	campaign_id = ?
                	AND ad_position = ?
                	AND time_type = ?
                	AND delay_value <= ?
                	AND !bitmap_contains ((SELECT bitmap_union (plan_ads) FROM ad_plan_user WHERE plan_id = ? AND oneid = ?), main_id)
                
                """;
        if (campaignItemIds != null && campaignItemIds.size() > 0) {
            sql += " AND main_id not in (" + campaignItemIds.join(",") + ") ";
        }

        sql += """
                ORDER BY
                	delay_value ASC,
                	slot_time ASC
                	LIMIT ?
                """;

        DbList campaignItems = Db.query(ds, sql, campaignId, position, "DELAYDAYS", delayDays, planId, oneid, limitCount);
        List<Entity> creativeList = new ArrayList<>();
        List<Entity> taskList = new ArrayList<>();
        List<Entity> succCampaignItems = new ArrayList<>();
        List<Entity> failCampaignItems = new ArrayList<>();
        if (campaignItems.size() > 0) {
            for (Entity campaignItem : campaignItems) {
                Entity creative = PlanHelper.findCreativeByCampaignItem(ds, campaignItem, oneid);
                if (creative != null) {
                    creativeList.add(creative);
                    succCampaignItems.add(campaignItem);

                    Entity plan = Entity.create();
                    plan.set("main_id", planId);
                    plan.set("priority", priority);

                    Entity adTask = PlanHelper.buildAdTaskToUser (plan, touchUser, campaignItem, creative);
                    adTask.set("plan_user_id", planUserId);
                    taskList.add(adTask);

                } else {
                    failCampaignItems.add(campaignItem);
                }
            }
        } else {
            return null;
        }

        Map<String, List<Entity>> resultMap = new HashMap<>();
        resultMap.put("taskList", taskList);
        resultMap.put("succCampaignItems", succCampaignItems);
        resultMap.put("failCampaignItems", failCampaignItems);

        return resultMap;
    }




    public static int getDaysBetween(String timeStr) {
        try {
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            LocalDateTime pastTime = LocalDateTime.parse(timeStr, formatter);
            LocalDateTime now = LocalDateTime.now();

            // 转换为日期（去掉时分秒）后再计算差值
            LocalDate pastDate = pastTime.toLocalDate();
            LocalDate nowDate = now.toLocalDate();

            return (int) ChronoUnit.DAYS.between(pastDate, nowDate);
        } catch (Exception e) {
            // 根据你的需求处理异常
            // 可以返回-1表示错误，或者抛出异常
            return -1;
        }
    }






}
